﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using System.Data.OracleClient;
using System.Data;
using MarginTrading.Common;

namespace MarginTrading.Services
{
    public class DailyTransactionsServices
    {
        //const string DB_CONNECTION_STRING = "user id=MTDBUser;password=Donkey1;data source=192.168.137.84";
        //const string DB_CONNECTION_STRING = "user id=MTDBUser;password=Donkey1;data source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.84)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE)))"; 

        private static DailyTransactions GetDailyTransactionsFromDataReader(IDataReader dataReader)
        {
            DailyTransactions retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DailyTransactions();
                retVal.BuySell = dataReader["BUY_SELL"].ToString();
                retVal.ExecutionId = dataReader["EXECUTION_ID"].ToString();
                retVal.CompanySymbol = dataReader["COMPANY_SYMBOL"].ToString();
                retVal.BrokerIdBuy = dataReader["BROKER_ID_BUY"].ToString();
                retVal.BrokerIdSell = dataReader["BROKER_ID_SELL"].ToString();
                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();
                retVal.ParticipantId = dataReader["PARTICIPANT_ID"].ToString();
                retVal.OrderId = dataReader["ORDER_ID"].ToString();
                retVal.TranId = dataReader["TRAN_ID"].ToString();
                retVal.SettleStatus = dataReader["SETTLEMENT_STATUS"].ToString();

                DateTime tradeDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["TRADE_DATE"].ToString(), out tradeDate))
                    retVal.TradeDate = tradeDate;

                int quantity = 0;
                if (int.TryParse(dataReader["QUANTITY"].ToString(), out quantity))
                    retVal.Quantity = quantity;
                double price = 0;
                if (double.TryParse(dataReader["PRICE"].ToString(), out price))
                    retVal.Price = price;
                int taxFlag = 0;
                if (int.TryParse(dataReader["TAX_FLAG"].ToString(), out taxFlag))
                    retVal.TaxFlag = taxFlag;
                int buying_contract_no = 0;
                if (int.TryParse(dataReader["BUYING_CONTRACT_NO"].ToString(), out buying_contract_no))
                    retVal.BuyingContractNo = buying_contract_no;
                int selling_contract_no = 0;
                if (int.TryParse(dataReader["SELLING_CONTRACT_NO"].ToString(), out selling_contract_no))
                    retVal.SellingContractNo = selling_contract_no;
                int foreign_flag = 0;
                if (int.TryParse(dataReader["FOREIGN_FLAG"].ToString(), out foreign_flag))
                    retVal.ForeignFlag = foreign_flag;
                double amount = 0;
                if (double.TryParse(dataReader["AMOUNT"].ToString(), out amount))
                    retVal.Amount = amount;
                int status = 0;
                if (int.TryParse(dataReader["STATUS"].ToString(), out status))
                    retVal.Status = status;
                DateTime SettleDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["SETTLEMENT_DATE"].ToString(), out SettleDate))
                    retVal.SettleDate = SettleDate;
               
            }
            return retVal;
        }


 		public static DailyTransactions GetDailyTransactionsByExecIdAndDate(string ExecId, DateTime date) //RR Tran->Exec
        {
            DailyTransactions retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where EXECUTION_ID = :v1 and TRADE_DATE = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = ExecId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetDailyTransactionsFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }




        public static DailyTransactions GetDailyTransactionsByTrnIdAndDate(string TranId,DateTime date)
        {
            DailyTransactions retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where TRAN_ID = '" + TranId + "' and TRADE_DATE = '" + date + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetDailyTransactionsFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Update(DailyTransactions transaction)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE DAILY_TRANSACTIONS SET " +
                                  "CLIENT_CODE = :v2, " +
                                  "COMPANY_SYMBOL = :v3, " +
                                  "QUANTITY = :v4, " +
                                  "PRICE = :v5, " +
                                  "AMOUNT = :v6, " +
                                  "BUY_SELL = :v7 " +
                                  "WHERE EXECUTION_ID = :v1 AND TO_CHAR(TRADE_DATE, 'dd-mm-yyyy') = '" + transaction.TradeDate.ToString("dd-MM-yyyy") + "'";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = transaction.ExecutionId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = transaction.ClientCode;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = transaction.CompanySymbol;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = transaction.Quantity;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = transaction.Price;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = transaction.Amount;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = transaction.BuySell;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static DailyTransactionsCollection GetDailyTransactionsCollection()
        {
            DailyTransactionsCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DailyTransactionsCollection();
                    while (dataReader.Read())
                    {
                        DailyTransactions item = GetDailyTransactionsFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DailyTransactionsCollection GetDailyTransactionsByClientIdAndDate(string clientId, DateTime date)
        {
            DailyTransactionsCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "' and TRADE_DATE = '" + date + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DailyTransactionsCollection();
                    while (dataReader.Read())
                    {
                        DailyTransactions item = GetDailyTransactionsFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DailyTransactionsCollection GetDailyTransactionsByClientId(string clientId)
        {
            DailyTransactionsCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DailyTransactionsCollection();
                    while (dataReader.Read())
                    {
                        DailyTransactions item = GetDailyTransactionsFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DailyTransactionsCollection GetDailyTransactionsByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DailyTransactionsCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "' and TRADE_DATE >= '" + fromDate + "' and TRADE_DATE <= '" + toDate + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DailyTransactionsCollection();
                    while (dataReader.Read())
                    {
                        DailyTransactions item = GetDailyTransactionsFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetLoanAccountCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetLoanAccountCollectionDataTableByClientIdAndDate(string clientId, DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            DateTime nextDate = date.AddDays(1);

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                if (clientId == "*")
                {
                    cmd.CommandText = "select * from DAILY_TRANSACTIONS where TRADE_DATE > '" + date.ToString("dd-MMM-yyyy") + "' and TRADE_DATE < '" + nextDate.ToString("dd-MMM-yyyy") + "'";

                }
                else
                {
                    cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "' and TRADE_DATE > '" + date.ToString("dd-MMM-yyyy") + "' and TRADE_DATE < '" + nextDate.ToString("dd-MMM-yyyy") + "'";

                }
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetLoanAccountCollectionDataTableByClientId(string clientId)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetLoanAccountCollectionDataTableByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_TRANSACTIONS where CLIENT_CODE = '" + clientId + "' and TRADE_DATE >= '" + fromDate + "' and TRADE_DATE <= '" + toDate + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static void Add(DailyTransactions dailyTransactions)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into DAILY_TRANSACTIONS(BUY_SELL," +
                                                      "EXECUTION_ID," +
                                                      "COMPANY_SYMBOL," +
                                                      "BROKER_ID_BUY," +
                                                      "BROKER_ID_SELL," +
                                                      "CLIENT_CODE, " +
                                                      "PARTICIPANT_ID, " +
                                                      "ORDER_ID, " +
                                                      "TRAN_ID, " +
                                                      "TRADE_DATE, " +
                                                      "QUANTITY, " +
                                                      "PRICE, " +
                                                      "TAX_FLAG, " +
                                                      "BUYING_CONTRACT_NO, " +
                                                      "SELLING_CONTRACT_NO, " +
                                                      "FOREIGN_FLAG, " +
                                                      "AMOUNT, " +
                                                      "STATUS, " +
                                                      "SETTLEMENT_DATE) " +
                                                      "values(:v1,:v2,:v3,:v4,:v5,:v6," +
                                                      ":v7,:v8,:v9,:v10,:v11,:v12,:v13," +
                                                      ":v14,:v15,:v16,:v17,:v18,:V19)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dailyTransactions.BuySell;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dailyTransactions.ExecutionId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = dailyTransactions.CompanySymbol;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = dailyTransactions.BrokerIdBuy;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = dailyTransactions.BrokerIdSell;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = dailyTransactions.ClientCode;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = dailyTransactions.ParticipantId;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = dailyTransactions.OrderId;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = dailyTransactions.TranId;
                cmd.Parameters.Add(":v10", OracleType.DateTime).Value = dailyTransactions.TradeDate;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = dailyTransactions.Quantity;
                cmd.Parameters.Add(":v12", OracleType.Number).Value = dailyTransactions.Price;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = dailyTransactions.TaxFlag;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = dailyTransactions.BuyingContractNo;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = dailyTransactions.SellingContractNo;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = dailyTransactions.ForeignFlag;
                cmd.Parameters.Add(":v17", OracleType.Number).Value = dailyTransactions.Amount;
                cmd.Parameters.Add(":v18", OracleType.Number).Value = dailyTransactions.Status;
                cmd.Parameters.Add(":v19", OracleType.DateTime).Value = dailyTransactions.SettleDate;

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string exeID, DateTime date)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from DAILY_TRANSACTIONS where EXECUTION_ID ='" + exeID + "' and TO_CHAR(TRADE_DATE, 'dd-mm-yyyy') = '" + date.ToString("dd-MM-yyyy") + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static List<string> GetExecIDCol(DateTime TrDate)
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select EXECUTION_ID from daily_transactions where"+
                                   " TO_CHAR(trade_date,'mm/dd/yyyy') = '" + TrDate.ToString("MM/dd/yyyy") + "'";
                //cmd.Parameters.Add(":v1", OracleType.DateTime).Value = TrDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["EXECUTION_ID"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetSettlementDataTable(DateTime settlementDate, string brokerCode, string agentCode, string side)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                string columnBroker;
                string columnTotal;
                if (side == "B")
                {
                    columnBroker = "BROKER_ID_BUY";
                    columnTotal = "Total_Purchases";
                }
                else
                {
                    columnBroker = "BROKER_ID_SELL";
                    columnTotal = "Total_Sells";
                }

                //cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, SUM(DT.AMOUNT) AS " + columnTotal + " FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE DT." + columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND C.CODE = DT.CLIENT_CODE AND C.AGENT = '" + agentCode + "' AND DT.BUY_SELL = '" + side + "' AND DT.SETTLEMENT_STATUS = '0' GROUP BY DT.CLIENT_CODE, C.SURNAME, C.INITIALS";
                cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, CASE WHEN" + 
                    " SUM(DT.AMOUNT) < 0 THEN -1 * SUM(DT.AMOUNT) ELSE SUM(DT.AMOUNT) END AS "
                    + columnTotal + " FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE DT." +
                    columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" +
                    settlementDate.ToString("dd-MMM-yyyy") + "' AND C.CODE = DT.CLIENT_CODE AND C.AGENT = '"
                    + agentCode + "' AND DT.BUY_SELL = '" + side + "' AND DT.SETTLEMENT_STATUS = '0' " +
                    "GROUP BY DT.CLIENT_CODE, C.SURNAME, C.INITIALS";
                cmd.Connection = con;
                
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetSettledDataTable(DateTime settlementDate, string brokerCode, string side)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                string columnBroker;
                string columnTotal;
                if (side == "B")
                {
                    columnBroker = "BROKER_ID_BUY";
                    columnTotal = "Total_Purchases";
                }
                else
                {
                    columnBroker = "BROKER_ID_SELL";
                    columnTotal = "Total_Sells";
                }

                //cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, SUM(DT.AMOUNT) AS " + columnTotal + " FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE DT." + columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND C.CODE = DT.CLIENT_CODE AND C.AGENT = '" + agentCode + "' AND DT.BUY_SELL = '" + side + "' AND DT.SETTLEMENT_STATUS = '0' GROUP BY DT.CLIENT_CODE, C.SURNAME, C.INITIALS";
                cmd.CommandText = "SELECT DT.CLIENT_CODE, CASE WHEN" +
                    " SUM(DT.AMOUNT) < 0 THEN -1 * SUM(DT.AMOUNT) ELSE SUM(DT.AMOUNT) END AS "
                    + columnTotal + " FROM DAILY_TRANSACTIONS DT WHERE DT." +
                    columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" +
                    settlementDate.ToString("dd-MMM-yyyy") + "'  AND DT.BUY_SELL = '" 
                    + side + "' AND DT.SETTLEMENT_STATUS != '0' " +
                    "GROUP BY DT.CLIENT_CODE";

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetUnSettleDataTable(DateTime settlementDate, string brokerCode, string agentCode, string side)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                string columnBroker;
                string columnTotal;
                if (side == "B")
                {
                    columnBroker = "BROKER_ID_BUY";
                    columnTotal = "Total_Purchases";
                }
                else
                {
                    columnBroker = "BROKER_ID_SELL";
                    columnTotal = "Total_Sells";
                }

                //cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, SUM(DT.AMOUNT) AS " + columnTotal + " FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE DT." + columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND C.CODE = DT.CLIENT_CODE AND C.AGENT = '" + agentCode + "' AND DT.BUY_SELL = '" + side + "' AND DT.SETTLEMENT_STATUS = '0' GROUP BY DT.CLIENT_CODE, C.SURNAME, C.INITIALS";
                cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, CASE WHEN SUM(DT.AMOUNT) < 0 THEN -1 * SUM(DT.AMOUNT) ELSE SUM(DT.AMOUNT) END AS " + columnTotal + ", DT.SETTLEMENT_STATUS AS REFERENCE_NO FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE DT." + columnBroker + " = '" + brokerCode + "' AND DT.SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND C.CODE = DT.CLIENT_CODE AND C.AGENT = '" + agentCode + "' AND DT.BUY_SELL = '" + side + "' AND DT.SETTLEMENT_STATUS != '0' GROUP BY DT.CLIENT_CODE, C.SURNAME, C.INITIALS, DT.SETTLEMENT_STATUS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetInitialSettlementDataTable(string sBrokerCode, string sAgentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                Config config = ConfigServices.GetConfigByName(Constants.CONFIG_NAME_DEPLOYMENT_SIDE);
                if (config.Value == Constants.CONFIG_VALUE_DEPLOYMENT_SIDE_AGENT)
                {
                    cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, DT.AMOUNT FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE C.CODE = DT.CLIENT_CODE AND C.BROKER = '" 
                        + sBrokerCode + "' AND DT.SETTLEMENT_STATUS = '0' AND TRADE_DATE = '" + DateTime.MinValue.ToString("dd-MMM-yyyy") + "'";
                }
                else
                {
                    cmd.CommandText = "SELECT DT.CLIENT_CODE, C.SURNAME, C.INITIALS, DT.AMOUNT FROM DAILY_TRANSACTIONS DT, CLIENT C WHERE C.CODE = DT.CLIENT_CODE AND C.AGENT = '" 
                        + sAgentCode + "' AND DT.SETTLEMENT_STATUS = '0' AND TRADE_DATE = '" + DateTime.MinValue.ToString("dd-MMM-yyyy") + "'";
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Settle(string sClientCode, DateTime settlementDate, string brokerCode, string side, string referenceNO)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                string columnBroker;
                if (side == "B")
                {
                    columnBroker = "BROKER_ID_BUY";
                }
                else
                {
                    columnBroker = "BROKER_ID_SELL";
                }

                cmd.CommandText = "UPDATE DAILY_TRANSACTIONS SET SETTLEMENT_STATUS = '" + referenceNO + "' WHERE CLIENT_CODE = '" + sClientCode + "' AND " + columnBroker + " = '" + brokerCode + "' AND SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND BUY_SELL = '" + side + "'";

                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

		public static void UnSettle(string sClientCode, DateTime settlementDate, string brokerCode, string side, string referenceNO)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                string columnBroker;
                if (side == "B")
                {
                    columnBroker = "BROKER_ID_BUY";
                }
                else
                {
                    columnBroker = "BROKER_ID_SELL";
                }

                cmd.CommandText = "UPDATE DAILY_TRANSACTIONS SET SETTLEMENT_STATUS = '0' WHERE CLIENT_CODE = '" + sClientCode + "' AND " + columnBroker + " = '" + brokerCode + "' AND SETTLEMENT_DATE = '" + settlementDate.ToString("dd-MMM-yyyy") + "' AND BUY_SELL = '" + side + "' AND SETTLEMENT_STATUS = '" + referenceNO + "'";

                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static bool HasInitialAccBal(string sClientCode)
        {
            bool retVal = true;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "SELECT COUNT(EXECUTION_ID) AS CCC FROM DAILY_TRANSACTIONS WHERE CLIENT_CODE = '" + sClientCode + "' AND TRADE_DATE = '" + DateTime.MinValue.ToString("dd-MMM-yyyy") + "'";

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && dataReader.Read())
                    {
                        int count = int.Parse(dataReader["CCC"].ToString());
                        if (count == 0)
                        {
                            retVal = false;
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void AddInitialAccBal(string sClientCode, double fAmount, bool AgentSide)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                if (!AgentSide)
                {
                    //Current acc bal of MT system should be deduct from actual acc bal to get the initial acc bal.
                    Double CurrentAccBal = GetAccStmBalance(sClientCode);
                    fAmount = fAmount - CurrentAccBal;
                }
               
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                if (fAmount == 0)
                {
                    cmd.CommandText = "DELETE FROM DAILY_TRANSACTIONS WHERE CLIENT_CODE = '" + sClientCode + "' AND TRADE_DATE = '" + DateTime.MinValue.ToString("dd-MMM-yyyy") + "'";
                }
                else if (HasInitialAccBal(sClientCode))
                {
                    cmd.CommandText = "UPDATE DAILY_TRANSACTIONS SET AMOUNT = '" + fAmount + "' WHERE CLIENT_CODE = '" + sClientCode + "' AND TRADE_DATE = '" + DateTime.MinValue.ToString("dd-MMM-yyyy") + "'";
                }
                else
                {
                    //string sTimestamp = DateTime.Now.Ticks.ToString(); Ravin
                    //sTimestamp = sTimestamp.Substring(2, 16);
                    string sTimestamp = sClientCode.Replace("-", "");

                    cmd.CommandText = "INSERT INTO DAILY_TRANSACTIONS(EXECUTION_ID, TRADE_DATE, TRAN_ID, CLIENT_CODE, AMOUNT) VALUES(:v1, :v2, '0', :v3, :v4)";
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = sTimestamp;
                    cmd.Parameters.Add(":v2", OracleType.DateTime).Value = DateTime.MinValue;
                    cmd.Parameters.Add(":v3", OracleType.VarChar).Value = sClientCode;
                    cmd.Parameters.Add(":v4", OracleType.Number).Value = fAmount;
                }
                
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        //TODO:Implement update method
        //public static void Update(LoanAccount loanAccount)
        //{
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "update loan_account set " +
        //                          "CLIENT_CODE = :v2" +
        //                          "ENTRY_TYPE = :v3" +
        //                          "REMARKS = :v4" +
        //                          "AMOUNT = :v5" +
        //                          "CREATED_DATE = :v6 where REFERENCE_NO = :v1";
        //        cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dailyTransactions.BuySell;
        //        cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dailyTransactions.ExecutionId;
        //        cmd.Parameters.Add(":v3", OracleType.VarChar).Value = dailyTransactions.CompanySymbol;
        //        cmd.Parameters.Add(":v4", OracleType.VarChar).Value = dailyTransactions.BrokerIdBuy;
        //        cmd.Parameters.Add(":v5", OracleType.Number).Value = dailyTransactions.BrokerIdSell;
        //        cmd.Parameters.Add(":v6", OracleType.DateTime).Value = dailyTransactions.ClientCode;
        //        cmd.Parameters.Add(":v7", OracleType.VarChar).Value = dailyTransactions.ParticipantId;
        //        cmd.Parameters.Add(":v8", OracleType.VarChar).Value = dailyTransactions.OrderId;
        //        cmd.Parameters.Add(":v9", OracleType.VarChar).Value = dailyTransactions.TranId;
        //        cmd.Parameters.Add(":v10", OracleType.VarChar).Value = dailyTransactions.TradeDate;
        //        cmd.Parameters.Add(":v11", OracleType.Number).Value = dailyTransactions.Quantity;
        //        cmd.Parameters.Add(":v12", OracleType.DateTime).Value = dailyTransactions.Price;
        //        cmd.Parameters.Add(":v13", OracleType.DateTime).Value = dailyTransactions.TaxFlag;
        //        cmd.Parameters.Add(":v14", OracleType.DateTime).Value = dailyTransactions.BuyingContractNo;
        //        cmd.Parameters.Add(":v15", OracleType.DateTime).Value = dailyTransactions.SellingContractNo;
        //        cmd.Parameters.Add(":v16", OracleType.DateTime).Value = dailyTransactions.ForeignFlag;
        //        cmd.Parameters.Add(":v17", OracleType.DateTime).Value = dailyTransactions.Amount;
        //        cmd.Parameters.Add(":v18", OracleType.DateTime).Value = dailyTransactions.Status;

        //        cmd.Connection = con;
        //        cmd.ExecuteNonQuery();

        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}


        public static Double GetAccStmBalance(String ClientCode)
        {
            Double retVal = 0.0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select sum(AMOUNT) from daily_transactions where" +
                                   " CLIENT_CODE = '" + ClientCode + "'";
                
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {                    
                    if (dataReader.Read())
                    {
                        string item = dataReader[0].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal = Double.Parse(item);
                    }
                }


                OracleCommand cmd1 = new OracleCommand();
                cmd1.CommandText = "select sum(amount) from loan_account where ENTRY_TYPE in "+
                                   "('PA','RC', 'IN') and CLIENT_CODE = '" + ClientCode + "'";

                cmd1.Connection = con;

                using (IDataReader dataReader1 = cmd1.ExecuteReader())
                {
                    if (dataReader1.Read())
                    {
                        string item = dataReader1[0].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal = Double.Parse(item) + retVal;
                    }
                }


            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }




    }
}
